USE [master]
GO

/****** Object:  Database [SSRMDB]    Script Date: 07/07/2012 11:12:20 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SSRMDB')
DROP DATABASE [SSRMDB]
GO

USE [master]
GO

/****** Object:  Database [SSRMDB]    Script Date: 07/07/2012 11:12:20 ******/
CREATE DATABASE [SSRMDB] ON  PRIMARY 
( NAME = N'SSRMDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SSRMDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SSRMDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SSRMDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [SSRMDB] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SSRMDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [SSRMDB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [SSRMDB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [SSRMDB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [SSRMDB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [SSRMDB] SET ARITHABORT OFF 
GO

ALTER DATABASE [SSRMDB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [SSRMDB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [SSRMDB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [SSRMDB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [SSRMDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [SSRMDB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [SSRMDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [SSRMDB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [SSRMDB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [SSRMDB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [SSRMDB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [SSRMDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [SSRMDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [SSRMDB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [SSRMDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [SSRMDB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [SSRMDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [SSRMDB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [SSRMDB] SET  READ_WRITE 
GO

ALTER DATABASE [SSRMDB] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [SSRMDB] SET  MULTI_USER 
GO

ALTER DATABASE [SSRMDB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [SSRMDB] SET DB_CHAINING OFF 
GO
------------------------------------------------------------------------ USERS
USE [SSRMDB]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_Id]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_Id]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_LastName]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_LastName]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_Email]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_Email]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_Skype]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_Skype]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_Role]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_Role]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_Status]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_Status]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_OnLine]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_OnLine]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_USERS_TimeSpan]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[USERS] DROP CONSTRAINT [DF_USERS_TimeSpan]
END

GO

USE [SSRMDB]
GO

/****** Object:  Table [dbo].[USERS]    Script Date: 07/07/2012 11:20:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERS]') AND type in (N'U'))
DROP TABLE [dbo].[USERS]
GO

USE [SSRMDB]
GO

/****** Object:  Table [dbo].[USERS]    Script Date: 07/07/2012 11:20:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[USERS](
	[Id] [uniqueidentifier] NOT NULL,
	[Login] [nchar](30) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Email] [nvarchar](50) NOT NULL,
	[Skype] [nvarchar](30) NOT NULL,
	[Role] [tinyint] NOT NULL,
	[Password] [int] NOT NULL,
	[Status] [tinyint] NOT NULL,
	[OnLine] [bit] NOT NULL,
	[MsgTimeSpan] [time](7) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_Id]  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_LastName]  DEFAULT (N' ') FOR [LastName]
GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_Email]  DEFAULT (N' ') FOR [Email]
GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_Skype]  DEFAULT (N' ') FOR [Skype]
GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_Role]  DEFAULT ((1)) FOR [Role]
GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_Status]  DEFAULT ((0)) FOR [Status]
GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_OnLine]  DEFAULT ((0)) FOR [OnLine]
GO

ALTER TABLE [dbo].[USERS] ADD  CONSTRAINT [DF_USERS_TimeSpan]  DEFAULT ('') FOR [MsgTimeSpan]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_Id]    Script Date: 07/08/2012 17:42:10 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[USERS]') AND name = N'IND_Id')
DROP INDEX [IND_Id] ON [dbo].[USERS] WITH ( ONLINE = OFF )
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_Id]    Script Date: 07/08/2012 17:42:10 ******/
CREATE UNIQUE CLUSTERED INDEX [IND_Id] ON [dbo].[USERS] 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_Login]    Script Date: 07/08/2012 17:42:28 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[USERS]') AND name = N'IND_Login')
DROP INDEX [IND_Login] ON [dbo].[USERS] WITH ( ONLINE = OFF )
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_Login]    Script Date: 07/08/2012 17:42:28 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IND_Login] ON [dbo].[USERS] 
(
	[Login] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------------------------------------------------------------------

---  RESOURCES
USE [SSRMDB]
GO

/****** Object:  Table [dbo].[RESOURCES]    Script Date: 07/08/2012 17:52:03 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RESOURCES]') AND type in (N'U'))
DROP TABLE [dbo].[RESOURCES]
GO

USE [SSRMDB]
GO

/****** Object:  Table [dbo].[RESOURCES]    Script Date: 07/08/2012 17:52:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RESOURCES](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[StartTime] [time](7) NOT NULL,
	[EndTime] [time](7) NOT NULL,
	[Locked] [bit] NOT NULL,
	[TimeOfUsing] [time](7) NOT NULL,
	[UserCount] [int] NOT NULL,
	[OnlyAutoTime] [bit] NOT NULL
) ON [PRIMARY]

GO
USE [SSRMDB]
GO

/****** Object:  Index [IND_Id]    Script Date: 07/08/2012 17:52:29 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[RESOURCES]') AND name = N'IND_Id')
DROP INDEX [IND_Id] ON [dbo].[RESOURCES] WITH ( ONLINE = OFF )
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_Id]    Script Date: 07/08/2012 17:52:29 ******/
CREATE UNIQUE CLUSTERED INDEX [IND_Id] ON [dbo].[RESOURCES] 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [SSRMDB]
GO

/****** Object:  Index [IND_Name]    Script Date: 07/08/2012 17:53:00 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[RESOURCES]') AND name = N'IND_Name')
DROP INDEX [IND_Name] ON [dbo].[RESOURCES] WITH ( ONLINE = OFF )
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_Name]    Script Date: 07/08/2012 17:53:00 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IND_Name] ON [dbo].[RESOURCES] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

-------------------------------------------------------------------------------------------------------------------------------------
-- QUEUES
USE [SSRMDB]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QUEUES_RESOURCES]') AND parent_object_id = OBJECT_ID(N'[dbo].[QUEUES]'))
ALTER TABLE [dbo].[QUEUES] DROP CONSTRAINT [FK_QUEUES_RESOURCES]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QUEUES_USERS]') AND parent_object_id = OBJECT_ID(N'[dbo].[QUEUES]'))
ALTER TABLE [dbo].[QUEUES] DROP CONSTRAINT [FK_QUEUES_USERS]
GO

USE [SSRMDB]
GO

/****** Object:  Table [dbo].[QUEUES]    Script Date: 07/09/2012 12:49:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QUEUES]') AND type in (N'U'))
DROP TABLE [dbo].[QUEUES]
GO

USE [SSRMDB]
GO

/****** Object:  Table [dbo].[QUEUES]    Script Date: 07/09/2012 12:49:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[QUEUES](
	[Id] [uniqueidentifier] NOT NULL,
	[IdResource] [uniqueidentifier] NOT NULL,
	[IdUser] [uniqueidentifier] NOT NULL,
	[StartTime] [datetime2] NOT NULL,
	[FinishTime] [datetime2] NOT NULL,
	[MessageTime] [datetime2] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[QUEUES]  WITH CHECK ADD  CONSTRAINT [FK_QUEUES_RESOURCES] FOREIGN KEY([IdResource])
REFERENCES [dbo].[RESOURCES] ([Id])
GO

ALTER TABLE [dbo].[QUEUES] CHECK CONSTRAINT [FK_QUEUES_RESOURCES]
GO

ALTER TABLE [dbo].[QUEUES]  WITH CHECK ADD  CONSTRAINT [FK_QUEUES_USERS] FOREIGN KEY([IdUser])
REFERENCES [dbo].[USERS] ([Id])
GO

ALTER TABLE [dbo].[QUEUES] CHECK CONSTRAINT [FK_QUEUES_USERS]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_ID]    Script Date: 07/09/2012 17:26:15 ******/
CREATE UNIQUE CLUSTERED INDEX [IND_ID] ON [dbo].[QUEUES] 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_IdRes]    Script Date: 07/09/2012 17:26:32 ******/
CREATE NONCLUSTERED INDEX [IND_IdRes] ON [dbo].[QUEUES] 
(
	[IdResource] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_IdUser]    Script Date: 07/09/2012 17:26:55 ******/
CREATE NONCLUSTERED INDEX [IND_IdUser] ON [dbo].[QUEUES] 
(
	[IdUser] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_MsgTime]    Script Date: 07/09/2012 17:28:04 ******/
CREATE NONCLUSTERED INDEX [IND_MsgTime] ON [dbo].[QUEUES] 
(
	[MessageTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---- MESSAGES
USE [SSRMDB]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MESSAGES_USERS]') AND parent_object_id = OBJECT_ID(N'[dbo].[MESSAGES]'))
ALTER TABLE [dbo].[MESSAGES] DROP CONSTRAINT [FK_MESSAGES_USERS]
GO

USE [SSRMDB]
GO

/****** Object:  Table [dbo].[MESSAGES]    Script Date: 07/10/2012 22:18:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MESSAGES]') AND type in (N'U'))
DROP TABLE [dbo].[MESSAGES]
GO

USE [SSRMDB]
GO

/****** Object:  Table [dbo].[MESSAGES]    Script Date: 07/10/2012 22:18:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MESSAGES](
	[Id] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[MessageText] [nvarchar](500) NOT NULL,
	[CreateTime] [datetime2](7) NOT NULL,
	[SendedEmail] [datetime2](7) NULL,
	[SendedSkype] [datetime2](7) NULL,
	[EmailOk] [bit] NOT NULL,
	[SkypeOk] [bit] NOT NULL,
	[SendOk] [bit] NOT NULL
) ON [PRIMARY]

GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_ID]    Script Date: 07/10/2012 22:18:31 ******/
CREATE UNIQUE CLUSTERED INDEX [IND_ID] ON [dbo].[MESSAGES] 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_USER]    Script Date: 07/10/2012 22:18:50 ******/
CREATE NONCLUSTERED INDEX [IND_USER] ON [dbo].[MESSAGES] 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [SSRMDB]
GO

/****** Object:  Index [IND_SEND_OK]    Script Date: 07/10/2012 22:20:55 ******/
CREATE NONCLUSTERED INDEX [IND_SEND_OK] ON [dbo].[MESSAGES] 
(
	[SendOk] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


ALTER TABLE [dbo].[MESSAGES]  WITH CHECK ADD  CONSTRAINT [FK_MESSAGES_USERS] FOREIGN KEY([UserId])
REFERENCES [dbo].[USERS] ([Id])
GO

ALTER TABLE [dbo].[MESSAGES] CHECK CONSTRAINT [FK_MESSAGES_USERS]
GO
-----------------------------------------------------------------------------------------------------------------------------------------------------
----- PRIMARY KEYS
USE SSRMDB
ALTER TABLE Messages
ADD CONSTRAINT pk_mes_id PRIMARY KEY(Id)
GO

USE SSRMDB
ALTER TABLE USERS
ADD CONSTRAINT pk_us_id PRIMARY KEY(Id)
GO

USE SSRMDB
ALTER TABLE RESOURCES
ADD CONSTRAINT pk_res_id PRIMARY KEY(Id)
GO

USE SSRMDB
ALTER TABLE QUEUES
ADD CONSTRAINT pk_que_id PRIMARY KEY(Id)
GO






